package com.studorm.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.regex.Pattern;

import com.opensymphony.xwork2.ActionSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.studorm.Connection_mysql;
import com.studorm.vo.Repairs;


public class RepairsAction extends ActionSupport {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -3697921271744988026L;
	// 此变量为存放你要读取的表格数据，前台请求后会自动生成JSON格式发送到客户端
	private List<Repairs> tableRow = new ArrayList<Repairs>();
	private int amountOfRow = 0;
	public List<Repairs> getTableRow() {
		return tableRow;
	}
	public void setTableRow(List<Repairs> tableRow) {
		this.tableRow = tableRow;
	}

	public String loadRepairs() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		String year = request.getParameter("year");
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null || year == null){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps;
		ResultSet res;
		String sql;

		
		/* ------------------------
		 * 	此处得到总数据条数，用来计算页数
		 * ------------------------
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT COUNT(*) FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?  AND stuReport=?";
			ps = conn.prepareStatement(sql);

		}else{
			sql = "SELECT COUNT(*) FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?  AND stuReport=? AND dormNu=?";
			ps = conn.prepareStatement(sql);
			ps.setString(4, dormNu);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);
		 ps.setInt(3, 0);
		res = ps.executeQuery();
		while(res.next()){
			amountOfRow = res.getInt(1);
		}
		res.close();
		ps.close();

		/* －－－－－－－－－－－－－－－－－－－
		 * 此处从数据库读取前台请求的某页的数据
		 * －－－－－－－－－－－－－－－－－－－
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT id, dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost,isrepairs FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=? AND stuReport=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(4, (nuOfP-1) * rowEachP);
			ps.setInt(5, rowEachP);
		}else{
			sql = "SELECT id, dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost,isrepairs FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?   AND stuReport=? AND dormNu=?  LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setString(4, dormNu);
			ps.setInt(5, (nuOfP-1) * rowEachP);
			ps.setInt(6, rowEachP);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);
        ps.setInt(3, 0);
		
		res = ps.executeQuery();
		while(res.next()){
			Repairs u = new Repairs();
			u.setId(res.getInt("id"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setStudentNu(res.getString("studentNu"));
			u.setGoodsNu(res.getString("goodsNu"));
			u.setReasonOfRe(res.getString("reasonOfRe"));
			u.setSubmitDate(res.getString("submitDate"));
			u.setRepairsCost(res.getDouble("repairsCost"));
			u.setIsrepairs(res.getInt("isrepairs"));	
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
	
	/* ---------------------------
	 * 		删除修理数据 ver.12.8
	 * ---------------------------
	 * 
	 * 从前台ajax请求获得delId参数
	 * 此参数为修理记录的ID，通过此ID
	 * 用SQL语句删除数据库中的记录
	 */
	public String delRepairs() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String uId = request.getParameter("delId");
		int id = 0;
		if(uId != null){
			id = Integer.parseInt(uId);
		}else{
			return NONE;
		}
		Connection conn = Connection_mysql.getConnection();
		String sql = "DELETE FROM repairs WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res == 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	
	public int getAmountOfRow() {
		return amountOfRow;
	}
	public void setAmountOfRow(int amountOfRow) {
		this.amountOfRow = amountOfRow;
	}
	
	/* -----------------------------
	 * 	只读取一行指定水电费记录 ver.11.24
	 * -----------------------------
	 * 
	 */
	public String loadARepairs() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String repairsId = request.getParameter("editId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(repairsId == null || repairsId.isEmpty() || !patternNu.matcher(repairsId).matches()){
			return NONE;
		}
		String sql = "SELECT id, dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost,isrepairs FROM studormcms.reapairsmanage WHERE id = ?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(repairsId));
		ResultSet res = ps.executeQuery();
		while(res.next()){
			Repairs u = new Repairs();
			u.setId(res.getInt("id"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setStudentNu(res.getString("studentNu"));
			u.setGoodsNu(res.getString("goodsNu"));
			u.setReasonOfRe(res.getString("reasonOfRe"));
			u.setSubmitDate(res.getString("submitDate"));
			u.setRepairsCost(res.getDouble("repairsCost"));
			u.setIsrepairs(res.getInt("isrepairs"));	
			tableRow.add(u);			
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	/* --------------------------------
	 * 		编辑水电费信息 ver.12.13
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String editRepairs() throws Exception{
		
		Calendar rightNow = Calendar.getInstance();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		String submitDate = fmt.format(rightNow.getTime());
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String studentNu = request.getParameter("studentNu");
		String goodsNu = request.getParameter("goodsNu");
		String reasonOfRe = request.getParameter("reasonOfRe");
		String repairsCost = request.getParameter("repairsCost");
		String id = request.getParameter("uid");
        
		Pattern patternNu = Pattern.compile("\\d*");
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		if(studentNu == null || id.isEmpty() || !patternNu.matcher(studentNu).matches()){
			return NONE;
		}
		if(goodsNu == null || id.isEmpty() || !patternNu.matcher(goodsNu).matches()){
			return NONE;
		}
	if(repairsCost == null || repairsCost.isEmpty() || !patternDeci.matcher(repairsCost).matches()){
			return NONE;
		}
	
	
	
		Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE repairs SET studentNu=?,goodsNu=?,reasonOfRe=?,submitDate=?,repairsCost=? WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setString(1, studentNu);
		ps.setString(2, goodsNu);
		ps.setString(3, reasonOfRe);
		ps.setString(4, submitDate);
		ps.setDouble(5, Double.parseDouble(repairsCost));  
		ps.setInt(6, Integer.parseInt(id));
		
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	
	/* --------------------------------
	 * 		添加水电费信息 ver.11.23
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String addRepairs() throws Exception{
		
		Calendar rightNow = Calendar.getInstance();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		String submitDate = fmt.format(rightNow.getTime());

		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName");
		String dormNu = request.getParameter("dormNu");
		String studentNu = request.getParameter("studentNu");
		String goodsNu = request.getParameter("goodsNu");
		String reasonOfRe = request.getParameter("reasonOfRe");
		String repairsCost = request.getParameter("repairsCost");

		Pattern patternNu = Pattern.compile("\\d{3}");// 匹配3位整数
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		if(dormBdName.isEmpty() || dormBdName.equals("请选择")){
			return NONE;
		}
		if(dormNu == null || dormNu.isEmpty() || !patternNu.matcher(dormNu).matches()){
			return NONE;
		}
	
		if(studentNu == null ||studentNu.isEmpty()){
			return NONE;
		}
		if(goodsNu == null || goodsNu.isEmpty()){
			return NONE;
		}
		if(reasonOfRe == null || reasonOfRe.isEmpty()){
			return NONE;
		}
	if(repairsCost == null || repairsCost.isEmpty() || !patternDeci.matcher(repairsCost).matches()){
			return NONE;
		}
	

		Connection conn = Connection_mysql.getConnection();
		String sql = "INSERT INTO repairs (dorm,studentNu,goodsNu,reasonOfRe,submitDate,repairsCost,isrepairs,stuReport) VALUES ((SELECT id FROM dorminf where dormBdName=? and dormNu=?),?,?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, dormBdName);
		ps.setString(2, dormNu);
		ps.setString(3, studentNu);
		ps.setString(4, goodsNu);
		ps.setString(5, reasonOfRe);
		ps.setString(6, submitDate);
		ps.setDouble(7, Double.parseDouble(repairsCost));  
		ps.setInt(8, 0);
		ps.setInt(9, 0);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	/* -----------------------------
	 * 		修改是否已维修 ver.12.16
	 * -----------------------------
	 * 
	 */
	public String alterisRepairs() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		String sql = "UPDATE repairs SET isrepairs = ? WHERE id=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(2, Integer.parseInt(id));
		if(status.equals("未维修")){
			ps.setInt(1, 1);
		}else if(status.equals("已维修")){
			ps.setInt(1, 0);
		}else{
			return NONE;
		}
		
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res > 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	public String loadStuRepairs() throws Exception
	{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		HttpSession session = request.getSession();
		String id = (String)session.getAttribute("id");
		String sign = request.getParameter("issignin");	
		int issign;
		if(id == null ||id.isEmpty()){		
			return NONE;
		}
		if(sign.equals("未维修"))
		{
			issign = 0;
		}
		else {
			issign=1;
		}
		String sql = "SELECT  dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost  FROM studormcms.reapairsmanage  WHERE studentNu = ? AND isrepairs=? AND stuReport=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.parseInt(id));
		ps.setInt(2,issign);
		ps.setInt(3, 0);
		ResultSet res = ps.executeQuery();
		while(res.next()){
			Repairs u = new Repairs();
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setStudentNu(res.getString("studentNu"));
			u.setGoodsNu(res.getString("goodsNu"));
			u.setReasonOfRe(res.getString("reasonOfRe"));
			u.setSubmitDate(res.getString("submitDate"));
			u.setRepairsCost(res.getDouble("repairsCost"));
			tableRow.add(u);		
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	public String stuRepairsReport() throws Exception
	{
		
		//获取时间
		Calendar rightNow = Calendar.getInstance();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		String submitDate = fmt.format(rightNow.getTime());
	  //通过ID获取宿舍号以及宿舍
		HttpServletRequest request = ServletActionContext.getRequest(); 
		HttpSession session = request.getSession();
		String studentNu = (String)session.getAttribute("id");
		String sql = "SELECT dormBdName,dormNu FROM studormcms.studentmanage where studentNu=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, studentNu);
		ResultSet res = ps.executeQuery();
		res.next();
		String dormBdName = res.getString("dormBdName");
		String dormNu=res.getString("dormNu");
		String goodsNu = "0";
		String reasonOfRe = request.getParameter("reasonOfRe");
		String repairsCost ="0";
		res.close();
		ps.close();
		conn.close();
		
		if(reasonOfRe.isEmpty() ){
			return NONE;
		}
				
		conn = Connection_mysql.getConnection();
	    sql = "INSERT INTO repairs (dorm,studentNu,goodsNu,reasonOfRe,submitDate,repairsCost,isrepairs,stuReport) VALUES ((SELECT id FROM dorminf where dormBdName=? and dormNu=?),?,?,?,?,?,?,?)";
		PreparedStatement ps1 = conn.prepareStatement(sql);
		ps1.setString(1, dormBdName);
		ps1.setString(2, dormNu);
		ps1.setString(3, studentNu);
		ps1.setString(4, goodsNu);
		ps1.setString(5, reasonOfRe);
		ps1.setString(6, submitDate);
		ps1.setDouble(7, Double.parseDouble(repairsCost));  
		ps1.setInt(8, 0);
		ps1.setInt(9, 1);
		int res1 = ps1.executeUpdate();
		ps.close();
		conn.close();
		
		if(res1 < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	public String loadStuReDispose() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		String year = request.getParameter("year");
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null || year == null){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps;
		ResultSet res;
		String sql;

		
		/* ------------------------
		 * 	此处得到总数据条数，用来计算页数
		 * ------------------------
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT COUNT(*) FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?  AND stuReport=?";
			ps = conn.prepareStatement(sql);

		}else{
			sql = "SELECT COUNT(*) FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?  AND stuReport=? AND dormNu=?";
			ps = conn.prepareStatement(sql);
			ps.setString(4, dormNu);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);
		 ps.setInt(3, 1);
		res = ps.executeQuery();
		while(res.next()){
			amountOfRow = res.getInt(1);
		}
		res.close();
		ps.close();

		/* －－－－－－－－－－－－－－－－－－－
		 * 此处从数据库读取前台请求的某页的数据
		 * －－－－－－－－－－－－－－－－－－－
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT id, dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost,isrepairs FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=? AND stuReport=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(4, (nuOfP-1) * rowEachP);
			ps.setInt(5, rowEachP);
		}else{
			sql = "SELECT id, dormBdName, dormNu, studentNu, goodsNu, reasonOfRe, submitDate, repairsCost,isrepairs FROM studormcms.reapairsmanage WHERE dormBdName=? AND year=?   AND stuReport=? AND dormNu=?  LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setString(4, dormNu);
			ps.setInt(5, (nuOfP-1) * rowEachP);
			ps.setInt(6, rowEachP);
		}
		ps.setString(1, dormBdName);
		ps.setString(2, year);
        ps.setInt(3, 1);
		
		res = ps.executeQuery();
		while(res.next()){
			Repairs u = new Repairs();
			u.setId(res.getInt("id"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setStudentNu(res.getString("studentNu"));
			u.setGoodsNu(res.getString("goodsNu"));
			u.setReasonOfRe(res.getString("reasonOfRe"));
			u.setSubmitDate(res.getString("submitDate"));
			u.setRepairsCost(res.getDouble("repairsCost"));
			u.setIsrepairs(res.getInt("isrepairs"));	
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
	/* --------------------------------
	 * 		编辑水电费信息 ver.12.13
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String editStuReDispose() throws Exception{
		
		Calendar rightNow = Calendar.getInstance();
		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		String submitDate = fmt.format(rightNow.getTime());
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String studentNu = request.getParameter("studentNu");
		String goodsNu = request.getParameter("goodsNu");
		String reasonOfRe = request.getParameter("reasonOfRe");
		String repairsCost = request.getParameter("repairsCost");
		String id = request.getParameter("uid");
        
		Pattern patternNu = Pattern.compile("\\d*");
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		if(studentNu == null || id.isEmpty() || !patternNu.matcher(studentNu).matches()){
			return NONE;
		}
		if(goodsNu == null || id.isEmpty() || !patternNu.matcher(goodsNu).matches()){
			return NONE;
		}
	if(repairsCost == null || repairsCost.isEmpty() || !patternDeci.matcher(repairsCost).matches()){
			return NONE;
		}
	
	
	
		Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE repairs SET studentNu=?,goodsNu=?,reasonOfRe=?,submitDate=?,repairsCost=? ,stuReport=? WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setString(1, studentNu);
		ps.setString(2, goodsNu);
		ps.setString(3, reasonOfRe);
		ps.setString(4, submitDate);
		ps.setDouble(5, Double.parseDouble(repairsCost));  
		ps.setInt(6, 0);
		ps.setInt(7, Integer.parseInt(id));
		
		
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
}
